package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Guarantee;
import com.xqgl.dao.GuaranteeDao;
import com.xqgl.util.ConnertionUtil;

public class GuaranteeDaoImp implements GuaranteeDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Guarantee> getAllUserInfos() {
		ArrayList<Guarantee> guarantees = new ArrayList<>();
		String sql = "select * from guarantee";
		try {
			statement = conn.createStatement();
			ResultSet rst = statement.executeQuery(sql);
			while(rst.next()) {
				Guarantee guarantee = new Guarantee();
				guarantee.setId(rst.getInt("id"));
				guarantee.setVillagename(rst.getString("villagename"));
				guarantee.setRepairper(rst.getString("repairper"));
				guarantee.setEquname(rst.getString("equname"));
				guarantee.setRepairdesc(rst.getString("repairdesc"));
				guarantee.setGuaranteectime(rst.getString("guaranteectime"));
				guarantee.setGuaranteestate(rst.getString("guaranteestate"));
				guarantees.add(guarantee);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return guarantees;
	}
	
	@Override
	public int add(Guarantee guarantee) throws SQLException {
		String sql = "insert into guarantee(villagename,repairper,equname,repairdesc,guaranteectime,guaranteestate)"
				+ "values(?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, guarantee.getVillagename());
		ps.setString(2, guarantee.getRepairper());
		ps.setString(3, guarantee.getEquname());
		ps.setString(4, guarantee.getRepairdesc());
		ps.setString(5, guarantee.getGuaranteectime());
		ps.setString(6, guarantee.getGuaranteestate());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(Guarantee guarantee) throws SQLException {
		String sql = "update guarantee set villagename = '"+guarantee.getVillagename()+"', repairper = '"+guarantee.getRepairper()+"', equname ='"+guarantee.getEquname()+"',"
				+ "repairdesc = '"+guarantee.getRepairdesc()+"', guaranteectime = '"+guarantee.getGuaranteectime()+"', guaranteestate = '"+guarantee.getGuaranteestate()+"' where id = "+guarantee.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from guarantee where id = "+id+" ";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Guarantee findById(int id) throws SQLException {
		Guarantee guarantee = new Guarantee();
		String sql = "select * from guarantee where id = ?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		while(set.next()){
			guarantee.setId(set.getInt("id"));
			guarantee.setVillagename(set.getString("villagename"));
			guarantee.setRepairper(set.getString("repairper"));
			guarantee.setEquname(set.getString("equname"));
			guarantee.setRepairdesc(set.getString("repairdesc"));
			guarantee.setGuaranteectime(set.getString("guaranteectime"));
			guarantee.setGuaranteestate(set.getString("guaranteestate"));
		}
		ps.close();
		return guarantee;
	}

}
